﻿using AuthorityManagement.Model.Dto.report;
using AuthorityManagement.Model;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System.Collections.Generic;
using System.Reflection;
using System.Threading.Tasks;
using System;
using System.Linq;
using Mapster;
using NetTaste;
using AutoMapper;
using AuthorityManagement.Model.Dto.FileDto;
using System.Diagnostics;
using System.Text.Json;
using Quartz.Util;
using AuthorityManagement.Helper;
using File = AuthorityManagement.Model.File;
using Newtonsoft.Json;
using System.Security.Cryptography.X509Certificates;
using static AuthorityManagement.Helper.RuturnHelper;
using System.Data;
using System.IO;

namespace AuthorityManagement.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ReportController : ControllerBase
    {
        private readonly ISqlSugarClient _Db;
        private IMapper _mapper;
        public ReportController(ISqlSugarClient db, IMapper mapper)
        {
            _Db = db;
            _mapper = mapper;
        }
        /// <summary>
        /// 通过年份获取各类型目标数.
        /// </summary>
        /// <param name="Year">年份.</param>
        /// <param name="Type">类型(kesu:客诉/kehu:客户)</param>
        /// <returns></returns>
        [HttpGet("Year")]
        public async Task<dynamic> GetInfo(int Year, string Type)
        {
            var targetEntityList = new List<QmsBusiMonthlyTargetReportEntity>();
            if (Type == "kesu")
            {
                targetEntityList = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == Year && (it.Type.Contains("整机") || it.Type.Contains("配件")) && it.DeleteMark == null).ToListAsync();
                if (targetEntityList.Count == 0)
                {
                    for (int i = 1; i <= 4; i++)
                    {
                        string type = string.Empty;
                        switch (i)
                        {
                            case 1:
                                type = "整机(正式)";
                                break;
                            case 2:
                                type = "整机(非正式)";
                                break;
                            case 3:
                                type = "配件(正式)";
                                break;
                            case 4:
                                type = "配件(非正式)";
                                break;
                        }
                        targetEntityList.Add(new QmsBusiMonthlyTargetReportEntity()
                        {
                            Type = type,
                            Year = Year,
                            Sort = i
                        });
                    }
                }
            }
            else
            {
                List<string> dictionaryDataList = await _Db.Queryable<DictionaryTypeEntity, DictionaryTypeEntity, DictionaryDataEntity>((a, b, c) => new JoinQueryInfos(
                JoinType.Left, a.Id == b.ParentId, JoinType.Left, b.Id == c.DictionaryTypeId))
                    .Where((a, b, c) => a.FullName == "客诉报表" && b.FullName == "客户数据字典" && a.DeleteMark == null && b.DeleteMark == null && c.DeleteMark == null)
                    .OrderBy((a, b, c) => c.SortCode)
                    .Select((a, b, c) => c.FullName).ToListAsync();

                targetEntityList = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == Year && (!it.Type.Contains("整机") && !it.Type.Contains("配件")) && it.DeleteMark == null).ToListAsync();
                if (targetEntityList.Count == 0)
                {
                    for (int i = 1; i <= dictionaryDataList.Count(); i++)
                    {
                        targetEntityList.Add(new QmsBusiMonthlyTargetReportEntity()
                        {
                            Type = dictionaryDataList[i - 1] + "(正式)",
                            Year = Year,
                            Sort = i
                        });
                        targetEntityList.Add(new QmsBusiMonthlyTargetReportEntity()
                        {
                            Type = dictionaryDataList[i - 1] + "(非正式)",
                            Year = Year,
                            Sort = i
                        });
                    }
                }
                else
                {
                    // 使用LINQ对targetEntityList进行分组，分组依据是Type字段中'('之前的字符串
                    var groupedByPrefix = targetEntityList.GroupBy(item =>
                    {
                        // 使用Split方法按'('分割字符串，并取第一个元素
                        var parts = item.Type.Split('(');
                        return parts.Length > 0 ? parts[0] : item.Type; // 防止没有'('的情况
                    })
                   .Select(group => group.Key).ToList();

                    // 遍历要检查的集合
                    foreach (string item in dictionaryDataList)
                    {
                        if (!groupedByPrefix.Contains(item))
                        {
                            // 如果当前年目标数为新增客户，则添加到targetEntityList中
                            targetEntityList.Add(new QmsBusiMonthlyTargetReportEntity()
                            {
                                Type = item + "(正式)",
                                Year = Year,
                                Sort = targetEntityList.Count() + 1
                            });
                            targetEntityList.Add(new QmsBusiMonthlyTargetReportEntity()
                            {
                                Type = item + "(非正式)",
                                Year = Year,
                                Sort = targetEntityList.Count() + 1
                            });
                        }
                    }
                }
            }

            return targetEntityList.OrderBy(o => o.Sort);
        }

        /// <summary>
        /// 新建客户投诉报表目标数.
        /// </summary>
        /// <param name="Type">类型(kesu:客诉/kehu:客户)</param>
        /// <param name="input">参数.</param>
        /// <returns></returns>
        [HttpPut("{Type}")]
        public async Task Create(string Type, [FromBody] List<QmsBusiMonthlyTargetReportCrInput> input)
        {
            var year = input[0].Year;
            var targetEntityList = new List<QmsBusiMonthlyTargetReportEntity>();
            if (Type == "kesu")
            {
                targetEntityList = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == year && (it.Type.Contains("整机") || it.Type.Contains("配件")) && it.DeleteMark == null).ToListAsync();
            }
            else
            {
                targetEntityList = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == year && (!it.Type.Contains("整机") && !it.Type.Contains("配件")) && it.DeleteMark == null).ToListAsync();
            }
            // 如果当前年已录入,删除重新录
            if (targetEntityList.Count > 0)
                await _Db.Deleteable(targetEntityList).ExecuteCommandAsync();

            var entityList = input.Adapt<List<QmsBusiMonthlyTargetReportEntity>>();

            #region 前端已卡住数据录入无需后端处理

            //// 使用HashSet来跟踪已经出现过的Sort值
            //HashSet<int> presentSorts = new HashSet<int>();

            //// 遍历List，将Sort值添加到HashSet中
            //foreach (var item in entityList)
            //{
            //    presentSorts.Add(item.Sort);
            //}

            //// 检查1到4之间哪些数字没有出现过
            //for (int i = 1; i <= 4; i++)
            //{
            //    if (!presentSorts.Contains(i))
            //    {
            //        QmsBusiMonthlyTargetReportEntity deficiencyEntity = new QmsBusiMonthlyTargetReportEntity();
            //        var type = string.Empty;
            //        switch (i)
            //        {
            //            case 1:
            //                type = "整机(正式)";
            //                break;
            //            case 2:
            //                type = "整机(非正式)";
            //                break;
            //            case 3:
            //                type = "配件(正式)";
            //                break;
            //            case 4:
            //                type = "配件(非正式)";
            //                break;
            //        }
            //        deficiencyEntity.Type = type;
            //        deficiencyEntity.Year = year;
            //        deficiencyEntity.Sort = i;
            //        entityList.Add(deficiencyEntity);
            //    }
            //}

            #endregion
            foreach (var item in entityList)
            {
                item.Id = Guid.NewGuid().ToString("N");
            }
            await _Db.Insertable(entityList).ExecuteCommandAsync();
            //int isOk = await _Db.Insertable(entityList).CallEntityMethod(m => m.Creator()).ExecuteCommandAsync();

            //if (!(isOk > 0)) throw Oops.Oh(ErrorCode.COM1000);
        }


        /// <summary>
        /// 通过年份获取各统计图数据.
        /// </summary>
        /// <param name="Year">年份.</param>
        /// <param name="ProblemAttribution">问题归属(整机/配件).</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> StatisticalChart([FromQuery] int Year, [FromQuery] string ProblemAttribution)
        {
            // 目标数据集合
            var targetEntity = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == Year && it.Type.Contains(ProblemAttribution) && it.DeleteMark == null).OrderBy(o => o.Sort).ToListAsync();
            if (targetEntity.Count == 0)
            {
                //throw Oops.Oh("当前年份未录入目标数,请维护!");
            }
            // 实绩数据集合
            var list = _Db.Ado.SqlQuery<dynamic>(@$"WITH Months AS (  
                                SELECT 1 AS Month  
                                UNION ALL SELECT 2  
                                UNION ALL SELECT 3  
                                UNION ALL SELECT 4  
	                            UNION ALL SELECT 5  
	                            UNION ALL SELECT 6  
	                            UNION ALL SELECT 7  
	                            UNION ALL SELECT 8  
	                            UNION ALL SELECT 9  
	                            UNION ALL SELECT 10  
	                            UNION ALL SELECT 11
                                UNION ALL SELECT 12  
                                ),  ComplaintCounts AS (  
                                SELECT  YEAR(F_FeedbackDate) AS Year,  
                                        MONTH(F_FeedbackDate) AS Month,  
                                        f_ComplaintType,  
                                        COUNT(*) AS TotalCount  
                                FROM qms_busi_customer_complaint  
                                WHERE [F_ProblemAttribution] = '{ProblemAttribution}' AND YEAR(F_FeedbackDate) = {Year}  and F_DeleteMark is null
                                GROUP BY YEAR(F_FeedbackDate), MONTH(F_FeedbackDate), [F_ComplaintType]  )  
                                SELECT  
                                        m.Month,  
                                        COALESCE(SUM(CASE WHEN c.f_ComplaintType = '正式' THEN c.TotalCount ELSE 0 END), 0) AS FormalCount, 
                                        COALESCE(SUM(CASE WHEN c.f_ComplaintType = '非正式' THEN c.TotalCount ELSE 0 END), 0) AS InformalCount  
                                FROM Months m  
                                LEFT JOIN ComplaintCounts c ON m.Month = c.Month AND c.Year = {Year}  
                                GROUP BY m.Month  ORDER BY m.Month;").ToList();

            List<QmsBusiMonthlyTargetReportOutput> outputList = new List<QmsBusiMonthlyTargetReportOutput>();

            #region 正式投诉
            QmsBusiMonthlyTargetReportOutput output = new QmsBusiMonthlyTargetReportOutput();
            output = targetEntity[0].Adapt<QmsBusiMonthlyTargetReportOutput>();
            output.ComplaintType = "正式投诉";
            output.Type = "目标";
            output.Total = targetEntity[0].CalculateSum();
            output.Sort = 1;

            QmsBusiMonthlyTargetReportOutput output2 = new QmsBusiMonthlyTargetReportOutput();
            output2.ComplaintType = "正式投诉";
            output2.Type = "实绩";
            output2.January = list[0].FormalCount;
            output2.February = list[1].FormalCount;
            output2.March = list[2].FormalCount;
            output2.April = list[3].FormalCount;
            output2.May = list[4].FormalCount;
            output2.June = list[5].FormalCount;
            output2.July = list[6].FormalCount;
            output2.August = list[7].FormalCount;
            output2.September = list[8].FormalCount;
            output2.October = list[9].FormalCount;
            output2.November = list[10].FormalCount;
            output2.December = list[11].FormalCount;
            output2.Total = list.Sum(s => s.FormalCount);
            output2.Sort = 2;

            QmsBusiMonthlyTargetReportOutput output3 = CalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportOutput), output, output2, "/");
            output3.ComplaintType = "正式投诉";
            output3.Type = "达成率";
            output3.Sort = 3;
            #endregion

            #region 非正式投诉
            QmsBusiMonthlyTargetReportOutput output4 = new QmsBusiMonthlyTargetReportOutput();
            output4 = targetEntity[1].Adapt<QmsBusiMonthlyTargetReportOutput>();
            output4.ComplaintType = "非正式投诉";
            output4.Type = "目标";
            output4.Total = targetEntity[1].CalculateSum();
            output4.Sort = 4;

            QmsBusiMonthlyTargetReportOutput output5 = new QmsBusiMonthlyTargetReportOutput();
            output5.ComplaintType = "非正式投诉";
            output5.Type = "实绩";
            output5.January = list[0].InformalCount;
            output5.February = list[1].InformalCount;
            output5.March = list[2].InformalCount;
            output5.April = list[3].InformalCount;
            output5.May = list[4].InformalCount;
            output5.June = list[5].InformalCount;
            output5.July = list[6].InformalCount;
            output5.August = list[7].InformalCount;
            output5.September = list[8].InformalCount;
            output5.October = list[9].InformalCount;
            output5.November = list[10].InformalCount;
            output5.December = list[11].InformalCount;
            output5.Total = list.Sum(s => s.InformalCount);
            output5.Sort = 5;

            QmsBusiMonthlyTargetReportOutput output6 = CalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportOutput), output4, output5, "/");
            output6.ComplaintType = "非正式投诉";
            output6.Type = "达成率";
            output6.Sort = 6;
            #endregion

            #region 客户投诉合计
            QmsBusiMonthlyTargetReportOutput output7 = CalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportOutput), output, output4, "+");
            output7.ComplaintType = "客户投诉合计";
            output7.Type = "目标";
            output7.Sort = 7;

            QmsBusiMonthlyTargetReportOutput output8 = CalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportOutput), output2, output5, "+");
            output8.ComplaintType = "客户投诉合计";
            output8.Type = "实绩";
            output8.Sort = 8;

            QmsBusiMonthlyTargetReportOutput output9 = CalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportOutput), output7, output8, "/");
            output9.ComplaintType = "客户投诉合计";
            output9.Type = "达成率";
            output9.Sort = 9;
            #endregion

            outputList.Add(output);
            outputList.Add(output2);
            outputList.Add(output3);
            outputList.Add(output4);
            outputList.Add(output5);
            outputList.Add(output6);
            outputList.Add(output7);
            outputList.Add(output8);
            outputList.Add(output9);

            return outputList.OrderBy(o => o.Sort);
        }

        /// <summary>
        /// 通过年份获取各折线图数据.
        /// </summary>
        /// <param name="Year">年份.</param>
        /// <param name="ProblemAttribution">问题归属(整机/配件).</param>
        /// <param name="ComplaintType">抱怨类型（正式/非正式）.</param>
        /// <returns></returns>
        [HttpGet("LineChart")]
        public async Task<dynamic> LineChart([FromQuery] int Year, [FromQuery] string ProblemAttribution, [FromQuery] string ComplaintType)
        {
            string targetType = ProblemAttribution + $"({ComplaintType})";
            // 目标数据集合
            var targetEntity = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == Year && it.Type == targetType && it.DeleteMark == null).OrderBy(o => o.Sort).ToListAsync();
            if (targetEntity.Count == 0)
            {
                //throw "";
                //throw Oops.Oh("当前年份未录入目标数,请维护!");
            }
            // 实绩数据集合
            var list = _Db.Ado.SqlQuery<dynamic>(@$"WITH Months AS (  
                                SELECT 1 AS Month  
                                UNION ALL SELECT 2  
                                UNION ALL SELECT 3  
                                UNION ALL SELECT 4  
	                            UNION ALL SELECT 5  
	                            UNION ALL SELECT 6  
	                            UNION ALL SELECT 7  
	                            UNION ALL SELECT 8  
	                            UNION ALL SELECT 9  
	                            UNION ALL SELECT 10  
	                            UNION ALL SELECT 11
                                UNION ALL SELECT 12  
                                ),  ComplaintCounts AS (  
                                SELECT  YEAR(F_FeedbackDate) AS Year,  
                                        MONTH(F_FeedbackDate) AS Month,  
                                        f_ComplaintType,  
                                        COUNT(*) AS TotalCount  
                                FROM qms_busi_customer_complaint  
                                WHERE [F_ProblemAttribution] = '{ProblemAttribution}' AND YEAR(F_FeedbackDate) = {Year}  and F_DeleteMark is null
                                GROUP BY YEAR(F_FeedbackDate), MONTH(F_FeedbackDate), [F_ComplaintType]  )  
                                SELECT  
                                        m.Month,  
                                        COALESCE(SUM(CASE WHEN c.f_ComplaintType = '正式' THEN c.TotalCount ELSE 0 END), 0) AS FormalCount, 
                                        COALESCE(SUM(CASE WHEN c.f_ComplaintType = '非正式' THEN c.TotalCount ELSE 0 END), 0) AS InformalCount  
                                FROM Months m  
                                LEFT JOIN ComplaintCounts c ON m.Month = c.Month AND c.Year = {Year}  
                                GROUP BY m.Month  ORDER BY m.Month;").ToList();

            List<QmsBusiMonthlyTargetReportLineOutput> outputList = new List<QmsBusiMonthlyTargetReportLineOutput>();

            QmsBusiMonthlyTargetReportLineOutput output = new QmsBusiMonthlyTargetReportLineOutput();
            output.name = "目标";
            output.type = "line";
            output.data = targetEntity[0].DataList();

            QmsBusiMonthlyTargetReportLineOutput output2 = new QmsBusiMonthlyTargetReportLineOutput();
            output2.name = "实绩";
            output2.type = "line";

            // 将list<dynamic> 转为list<int>
            output2.data = list.Select(s => ComplaintType == "正式" ? s.FormalCount : s.InformalCount).Select(item =>
            {
                if (item is int intValue)
                {
                    return intValue;
                }
                else
                {
                    return 0;
                }
            }).ToList();
            outputList.Add(output);
            outputList.Add(output2);
            return outputList;
        }

        /// <summary>
        /// 通过年份获取（客户）统计图数据.
        /// </summary>
        /// <param name="Year">年份.</param>
        /// <returns></returns>
        [HttpGet("CustomerStatisticalChart")]
        public async Task<dynamic> CustomerStatisticalChart([FromQuery] int Year)
        {
            // 目标数据集合 不包含整机/配件
            var targetEntity = await _Db.Queryable<QmsBusiMonthlyTargetReportEntity>().Where(it => it.Year == Year && (!it.Type.Contains("整机") && !it.Type.Contains("配件")) && it.DeleteMark == null).OrderBy(o => o.Sort).ToListAsync();
            if (targetEntity.Count() == 0)
            {
                //throw Oops.Oh("当前年份未录入目标数,请维护!");
            }

            // 实绩数据集合
            var list = _Db.Ado.SqlQuery<dynamic>(@$"
-- 创建一个包含所有月份的CTE  
WITH MonthsCTE AS (  
    SELECT 1 AS MonthNum  
    UNION ALL SELECT 2  
    UNION ALL SELECT 3  
    UNION ALL SELECT 4  
    UNION ALL SELECT 5  
    UNION ALL SELECT 6  
    UNION ALL SELECT 7  
    UNION ALL SELECT 8  
    UNION ALL SELECT 9  
    UNION ALL SELECT 10  
    UNION ALL SELECT 11  
    UNION ALL SELECT 12  
),  
-- 创建一个包含所有客户和抱怨类型的CTE（这里假设你已经有这些数据的列表）  
CustomersAndTypes AS (  
  
SELECT   
    m.F_EnCode as F_CustomerNo, 
	m.F_FullName as F_CustomerName,
	m.F_SortCode,
    v.f_ComplaintType -- 新增的列，区分正式和非正式
	
FROM   
    (select c.F_FullName,c.F_EnCode,c.F_SortCode from base_dictionarytype a
			left join base_dictionarytype b on a.F_Id=b.F_ParentId
			left join base_dictionarydata c on b.F_Id=c.F_DictionaryTypeId
where a.F_FullName='客诉报表' and b.F_FullName='客户数据字典' and a.F_DeleteMark is null and b.F_DeleteMark is null and c.F_DeleteMark is null) m
CROSS APPLY (VALUES   
        ('正式', m.F_EnCode,m.F_SortCode),  -- 保持原Value，但添加'正式'类型  
        ('非正式', m.F_EnCode,m.F_SortCode) -- 保持原Value，但添加'非正式'类型  
) v (f_ComplaintType, Value,sort) 

),  
-- 主查询  
MonthlyComplaints AS (  
    SELECT  
        YEAR(c.F_FeedbackDate) AS ComplaintYear,  
        MONTH(c.F_FeedbackDate) AS ComplaintMonth,  
        c.F_CustomerNo,  
		c.F_CustomerName,  
        c.F_ComplaintType,  
        COUNT(*) AS ComplaintCount  
    FROM  
        qms_busi_customer_complaint c  
    WHERE  
        YEAR(c.F_FeedbackDate) = {Year}  -- 指定年份  
    GROUP BY  
        YEAR(c.F_FeedbackDate),  
        MONTH(c.F_FeedbackDate),  
        c.F_CustomerNo,
		c.F_CustomerName,
        c.F_ComplaintType  
)  
-- 最终查询，通过LEFT JOIN确保所有月份都出现，缺失月份填充0  
SELECT  
    m.MonthNum AS Month,  
    ct.F_CustomerNo, 
	ct.F_CustomerName, 
	ct.F_SortCode,
    ct.F_ComplaintType,  
    ISNULL(SUM(mc.ComplaintCount), 0) AS TotalComplaints  
FROM  
    MonthsCTE m  
    CROSS JOIN CustomersAndTypes ct  
    LEFT JOIN MonthlyComplaints mc ON m.MonthNum = mc.ComplaintMonth AND ct.F_CustomerNo = mc.F_CustomerNo AND ct.F_ComplaintType = mc.F_ComplaintType  
WHERE  
    m.MonthNum BETWEEN 1 AND 12  -- 可选，因为MonthsCTE已经定义了这些月份  
GROUP BY  
    m.MonthNum,  
    ct.F_CustomerNo,  
	ct.F_CustomerName,
    ct.F_ComplaintType,
	ct.F_SortCode
ORDER BY  
    ct.F_SortCode,
    ct.F_CustomerNo, 
	ct.F_CustomerName,
    m.MonthNum,  
    ct.F_ComplaintType;").ToList();

            // 数据处理后总集合
            List<QmsBusiMonthlyTargetReportCustomerOutput> outputList = new List<QmsBusiMonthlyTargetReportCustomerOutput>();

            // 集合排序字段累加
            int baseSortOrder = 1;

            // 目标数集合 每个客户包含两种类型数据，因此在for循环时采用 i += 2 每次循环一次性处理两种类型数据
            for (int i = 1; i <= targetEntity.Count(); i += 2)
            {
                // 截取(前的客户名称
                int index = targetEntity[i - 1].Type.IndexOf("(");
                string customerName = targetEntity[i - 1].Type.Substring(0, index);

                // 客诉实际数据不需要所有客户的报表，只筛选已录入目标数客户的
                var formalList = list.Where(w => w.F_ComplaintType == "正式" && w.F_CustomerName == customerName).ToList();
                var informalList = list.Where(w => w.F_ComplaintType == "非正式" && w.F_CustomerName == customerName).ToList();

                #region 正式投诉

                QmsBusiMonthlyTargetReportCustomerOutput output = new QmsBusiMonthlyTargetReportCustomerOutput();
                output = targetEntity[i - 1].Adapt<QmsBusiMonthlyTargetReportCustomerOutput>();
                output.CustomerName = customerName;
                output.ComplaintType = "正式投诉";
                output.Type = "目标";
                output.Total = targetEntity[i - 1].CalculateSum();
                output.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output2 = new QmsBusiMonthlyTargetReportCustomerOutput();
                output2.CustomerName = customerName;
                output2.ComplaintType = "正式投诉";
                output2.Type = "实绩";
                output2.January = formalList[0].TotalComplaints;
                output2.February = formalList[1].TotalComplaints;
                output2.March = formalList[2].TotalComplaints;
                output2.April = formalList[3].TotalComplaints;
                output2.May = formalList[4].TotalComplaints;
                output2.June = formalList[5].TotalComplaints;
                output2.July = formalList[6].TotalComplaints;
                output2.August = formalList[7].TotalComplaints;
                output2.September = formalList[8].TotalComplaints;
                output2.October = formalList[9].TotalComplaints;
                output2.November = formalList[10].TotalComplaints;
                output2.December = formalList[11].TotalComplaints;
                output2.Total = formalList.Sum(s => s.TotalComplaints);
                output2.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output3 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), output, output2, "/");
                output3.CustomerName = customerName;
                output3.ComplaintType = "正式投诉";
                output3.Type = "达成率";
                output3.Sort = baseSortOrder++;
                #endregion

                #region 非正式投诉

                QmsBusiMonthlyTargetReportCustomerOutput output4 = new QmsBusiMonthlyTargetReportCustomerOutput();
                output4 = targetEntity[i].Adapt<QmsBusiMonthlyTargetReportCustomerOutput>();
                output4.CustomerName = customerName;
                output4.ComplaintType = "非正式投诉";
                output4.Type = "目标";
                output4.Total = targetEntity[i].CalculateSum();
                output4.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output5 = new QmsBusiMonthlyTargetReportCustomerOutput();
                output5.CustomerName = customerName;
                output5.ComplaintType = "非正式投诉";
                output5.Type = "实绩";
                output5.January = informalList[0].TotalComplaints;
                output5.February = informalList[1].TotalComplaints;
                output5.March = informalList[2].TotalComplaints;
                output5.April = informalList[3].TotalComplaints;
                output5.May = informalList[4].TotalComplaints;
                output5.June = informalList[5].TotalComplaints;
                output5.July = informalList[6].TotalComplaints;
                output5.August = informalList[7].TotalComplaints;
                output5.September = informalList[8].TotalComplaints;
                output5.October = informalList[9].TotalComplaints;
                output5.November = informalList[10].TotalComplaints;
                output5.December = informalList[11].TotalComplaints;
                output5.Total = informalList.Sum(s => s.TotalComplaints);
                output5.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output6 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), output4, output5, "/");
                output6.CustomerName = customerName;
                output6.ComplaintType = "非正式投诉";
                output6.Type = "达成率";
                output6.Sort = baseSortOrder++;
                #endregion

                #region 客户投诉合计

                QmsBusiMonthlyTargetReportCustomerOutput output7 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), output, output4, "+");
                output7.CustomerName = customerName;
                output7.ComplaintType = "客户投诉合计";
                output7.Type = "目标";
                output7.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output8 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), output2, output5, "+");
                output8.CustomerName = customerName;
                output8.ComplaintType = "客户投诉合计";
                output8.Type = "实绩";
                output8.Sort = baseSortOrder++;

                QmsBusiMonthlyTargetReportCustomerOutput output9 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), output7, output8, "/");
                output9.CustomerName = customerName;
                output9.ComplaintType = "客户投诉合计";
                output9.Type = "达成率";
                output9.Sort = baseSortOrder++;
                #endregion

                outputList.Add(output);
                outputList.Add(output2);
                outputList.Add(output3);
                outputList.Add(output4);
                outputList.Add(output5);
                outputList.Add(output6);
                outputList.Add(output7);
                outputList.Add(output8);
                outputList.Add(output9);
            }

            return CustomerFullDataSummary(outputList).OrderBy(o => o.Sort);
        }

        /// <summary>
        /// 计算所有客户正式/非正式(目标、实绩)总数
        /// </summary>
        /// <param name="outputList"></param>
        /// <returns></returns>
        private List<QmsBusiMonthlyTargetReportCustomerOutput> CustomerFullDataSummary(List<QmsBusiMonthlyTargetReportCustomerOutput> outputList)
        {
            List<QmsBusiMonthlyTargetReportCustomerOutput> outputTotalList = new List<QmsBusiMonthlyTargetReportCustomerOutput>();
            outputTotalList = outputList;

            var formalGoal = outputList.Where(w => w.ComplaintType == "正式投诉" && w.Type == "目标").ToList();
            var formalActual = outputList.Where(w => w.ComplaintType == "正式投诉" && w.Type == "实绩").ToList();
            var inFormalGoal = outputList.Where(w => w.ComplaintType == "非正式投诉" && w.Type == "目标").ToList();
            var inFormalActual = outputList.Where(w => w.ComplaintType == "非正式投诉" && w.Type == "实绩").ToList();

            int sortCount = outputList.Count();

            #region 正式投诉
            var outputTotal1 = new QmsBusiMonthlyTargetReportCustomerOutput()
            {
                CustomerName = "力隆中国",
                ComplaintType = "正式投诉",
                Type = "目标",
                January = formalGoal.Sum(w => Convert.ToInt32(w.January)),
                February = formalGoal.Sum(w => Convert.ToInt32(w.February)),
                March = formalGoal.Sum(w => Convert.ToInt32(w.March)),
                April = formalGoal.Sum(w => Convert.ToInt32(w.April)),
                May = formalGoal.Sum(w => Convert.ToInt32(w.May)),
                June = formalGoal.Sum(w => Convert.ToInt32(w.June)),
                July = formalGoal.Sum(w => Convert.ToInt32(w.July)),
                August = formalGoal.Sum(w => Convert.ToInt32(w.August)),
                September = formalGoal.Sum(w => Convert.ToInt32(w.September)),
                October = formalGoal.Sum(w => Convert.ToInt32(w.October)),
                November = formalGoal.Sum(w => Convert.ToInt32(w.November)),
                December = formalGoal.Sum(w => Convert.ToInt32(w.December)),
                Total = formalGoal.Sum(w => Convert.ToInt32(w.Total)),
                Sort = sortCount++
            };
            var outputTotal2 = new QmsBusiMonthlyTargetReportCustomerOutput()
            {
                CustomerName = "力隆中国",
                ComplaintType = "正式投诉",
                Type = "实绩",
                January = formalActual.Sum(w => Convert.ToInt32(w.January)),
                February = formalActual.Sum(w => Convert.ToInt32(w.February)),
                March = formalActual.Sum(w => Convert.ToInt32(w.March)),
                April = formalActual.Sum(w => Convert.ToInt32(w.April)),
                May = formalActual.Sum(w => Convert.ToInt32(w.May)),
                June = formalActual.Sum(w => Convert.ToInt32(w.June)),
                July = formalActual.Sum(w => Convert.ToInt32(w.July)),
                August = formalActual.Sum(w => Convert.ToInt32(w.August)),
                September = formalActual.Sum(w => Convert.ToInt32(w.September)),
                October = formalActual.Sum(w => Convert.ToInt32(w.October)),
                November = formalActual.Sum(w => Convert.ToInt32(w.November)),
                December = formalActual.Sum(w => Convert.ToInt32(w.December)),
                Total = formalActual.Sum(w => Convert.ToInt32(w.Total)),
                Sort = sortCount++
            };
            QmsBusiMonthlyTargetReportCustomerOutput outputTotal3 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), outputTotal1, outputTotal2, "/");
            outputTotal3.CustomerName = "力隆中国";
            outputTotal3.ComplaintType = "正式投诉";
            outputTotal3.Type = "达成率";
            outputTotal3.Sort = sortCount++;
            #endregion

            #region 非正式投诉
            var outputTotal4 = new QmsBusiMonthlyTargetReportCustomerOutput()
            {
                CustomerName = "力隆中国",
                ComplaintType = "非正式投诉",
                Type = "目标",
                January = inFormalGoal.Sum(w => Convert.ToInt32(w.January)),
                February = inFormalGoal.Sum(w => Convert.ToInt32(w.February)),
                March = inFormalGoal.Sum(w => Convert.ToInt32(w.March)),
                April = inFormalGoal.Sum(w => Convert.ToInt32(w.April)),
                May = inFormalGoal.Sum(w => Convert.ToInt32(w.May)),
                June = inFormalGoal.Sum(w => Convert.ToInt32(w.June)),
                July = inFormalGoal.Sum(w => Convert.ToInt32(w.July)),
                August = inFormalGoal.Sum(w => Convert.ToInt32(w.August)),
                September = inFormalGoal.Sum(w => Convert.ToInt32(w.September)),
                October = inFormalGoal.Sum(w => Convert.ToInt32(w.October)),
                November = inFormalGoal.Sum(w => Convert.ToInt32(w.November)),
                December = inFormalGoal.Sum(w => Convert.ToInt32(w.December)),
                Total = inFormalGoal.Sum(w => Convert.ToInt32(w.Total)),
                Sort = sortCount++
            };
            var outputTotal5 = new QmsBusiMonthlyTargetReportCustomerOutput()
            {
                CustomerName = "力隆中国",
                ComplaintType = "非正式投诉",
                Type = "实绩",
                January = inFormalActual.Sum(w => Convert.ToInt32(w.January)),
                February = inFormalActual.Sum(w => Convert.ToInt32(w.February)),
                March = inFormalActual.Sum(w => Convert.ToInt32(w.March)),
                April = inFormalActual.Sum(w => Convert.ToInt32(w.April)),
                May = inFormalActual.Sum(w => Convert.ToInt32(w.May)),
                June = inFormalActual.Sum(w => Convert.ToInt32(w.June)),
                July = inFormalActual.Sum(w => Convert.ToInt32(w.July)),
                August = inFormalActual.Sum(w => Convert.ToInt32(w.August)),
                September = inFormalActual.Sum(w => Convert.ToInt32(w.September)),
                October = inFormalActual.Sum(w => Convert.ToInt32(w.October)),
                November = inFormalActual.Sum(w => Convert.ToInt32(w.November)),
                December = inFormalActual.Sum(w => Convert.ToInt32(w.December)),
                Total = inFormalActual.Sum(w => Convert.ToInt32(w.Total)),
                Sort = sortCount++
            };

            QmsBusiMonthlyTargetReportCustomerOutput outputTotal6 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), outputTotal4, outputTotal5, "/");
            outputTotal6.CustomerName = "力隆中国";
            outputTotal6.ComplaintType = "非正式投诉";
            outputTotal6.Type = "达成率";
            outputTotal6.Sort = sortCount++;
            #endregion

            #region 客户投诉合计
            QmsBusiMonthlyTargetReportCustomerOutput outputTotal7 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), outputTotal1, outputTotal4, "+");
            outputTotal7.CustomerName = "力隆中国";
            outputTotal7.ComplaintType = "客户投诉合计";
            outputTotal7.Type = "目标";
            outputTotal7.Sort = sortCount++;

            QmsBusiMonthlyTargetReportCustomerOutput outputTotal8 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), outputTotal2, outputTotal5, "+");
            outputTotal8.CustomerName = "力隆中国";
            outputTotal8.ComplaintType = "客户投诉合计";
            outputTotal8.Type = "实绩";
            outputTotal8.Sort = sortCount++;

            QmsBusiMonthlyTargetReportCustomerOutput outputTotal9 = CustomerCalculatedAchievementRate(typeof(QmsBusiMonthlyTargetReportCustomerOutput), outputTotal7, outputTotal8, "/");
            outputTotal9.CustomerName = "力隆中国";
            outputTotal9.ComplaintType = "客户投诉合计";
            outputTotal9.Type = "达成率";
            outputTotal9.Sort = sortCount++;
            #endregion

            outputTotalList.Add(outputTotal1);
            outputTotalList.Add(outputTotal2);
            outputTotalList.Add(outputTotal3);
            outputTotalList.Add(outputTotal4);
            outputTotalList.Add(outputTotal5);
            outputTotalList.Add(outputTotal6);
            outputTotalList.Add(outputTotal7);
            outputTotalList.Add(outputTotal8);
            outputTotalList.Add(outputTotal9);

            return outputTotalList;
        }

        /// <summary>
        /// 通过反射计算数据并返回
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="first">第一个数据源</param>
        /// <param name="second">第二个数据源</param>
        /// <param name="CalculationMode">计算模式</param>
        /// <returns></returns>
        private QmsBusiMonthlyTargetReportOutput CalculatedAchievementRate(Type type, object first, object second, string CalculationMode)
        {
            QmsBusiMonthlyTargetReportOutput output = new QmsBusiMonthlyTargetReportOutput();
            Type rowType = type;
            PropertyInfo[] properties = rowType.GetProperties().Where(p => p.PropertyType == typeof(object)).ToArray(); // 只获取object类型的属性

            foreach (var prop in properties)
            {
                double firstValue = Convert.ToDouble(prop.GetValue(first, null));
                double secondValue = Convert.ToDouble(prop.GetValue(second, null));

                if (CalculationMode == "/")
                {
                    if (firstValue != 0) // 避免除以零
                    {
                        string percentage = ((secondValue / firstValue) * 100).ToString("F2") + "%";
                        prop.SetValue(output, percentage);
                    }
                    else
                    {
                        prop.SetValue(output, "0%");
                    }
                }
                else
                {
                    int percentage = Convert.ToInt32(secondValue + firstValue);
                    prop.SetValue(output, percentage);
                }
            }
            return output;
        }

        /// <summary>
        /// 通过反射计算数据并返回(客户报表)
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="first">第一个数据源</param>
        /// <param name="second">第二个数据源</param>
        /// <param name="CalculationMode">计算模式</param>
        /// <returns></returns>
        private QmsBusiMonthlyTargetReportCustomerOutput CustomerCalculatedAchievementRate(Type type, object first, object second, string CalculationMode)
        {
            QmsBusiMonthlyTargetReportCustomerOutput output = new QmsBusiMonthlyTargetReportCustomerOutput();
            Type rowType = type;
            PropertyInfo[] properties = rowType.GetProperties().Where(p => p.PropertyType == typeof(object)).ToArray(); // 只获取object类型的属性

            foreach (var prop in properties)
            {
                double firstValue = Convert.ToDouble(prop.GetValue(first, null));
                double secondValue = Convert.ToDouble(prop.GetValue(second, null));

                if (CalculationMode == "/")
                {
                    if (firstValue != 0) // 避免除以零
                    {
                        string percentage = ((secondValue / firstValue) * 100).ToString("F2") + "%";
                        prop.SetValue(output, percentage);
                    }
                    else
                    {
                        prop.SetValue(output, "0%");
                    }
                }
                else
                {
                    int percentage = Convert.ToInt32(secondValue + firstValue);
                    prop.SetValue(output, percentage);
                }
            }
            return output;
        }

        /// <summary>
        /// 测试Json.
        /// </summary>
        /// <param name="input">input</param>
        /// <returns></returns>
        [HttpPost("TestJson")]
        public async Task<dynamic> TestJson()
        {
            #region 方式一
            var data = await _Db.Queryable<File>().Where(it => it.Id == "1").Select(it => new FileDto { fileJson = it.FileInfo, type = it.Type, remark = it.Remark }).MergeTable().Mapper(m =>
             {
                 m.fileInfo = JsonConvert.DeserializeObject<List<FileControlsModel>>(m.fileJson);
             }).FirstAsync();
            #endregion

            #region 方式二
            //var data =await _Db.Queryable<File>().Where(it => it.Id == "1").Select(it => new FileDto { fileJson = it.FileInfo }).FirstAsync();
            ////序列化
            ////var json = Newtonsoft.Json.JsonConvert.SerializeObject(data);
            ////反序列化
            //var json = JsonConvert.DeserializeObject<List<FileControlsModel>>(data.fileJson);
            //data.fileInfo = json;
            #endregion
            return data;
        }
        /// <summary>
        /// 测试mapper.
        /// </summary>
        /// <param name="input">input</param>
        /// <returns></returns>
        [HttpPost("TestMapper")]
        public async Task<dynamic> TestMapper(Test input)
        {
            //var data = input.Adapt<User>();
            var data = _mapper.Map<User>(input);
            return data;
        }

        /// <summary>
        /// 测试在数据库中生成表
        /// </summary>
        /// <returns></returns>
        [HttpPost("DbFirst")]
        public async Task<dynamic> DbFirst()
        {
            //首先去判断数据库中这张表是否存在，存在则提醒有"已存在"，否则添加表
            if (IsTableExist<DbFirstTable>())
            {
                return RR.Success($"DbFirstTable表已存在！");
            }
            else
            {
                _Db.CodeFirst.InitTables(typeof(DbFirstTable));
                return RR.Success($"表DbFirstTable生成成功");
            }
        }

        /// <summary>
        /// 查看表是否存在
        /// </summary>
        /// <returns></returns>
        public bool IsTableExist<T>() where T : class, new()
        {
            Type type = typeof(T);
            string tableName = ((SugarTable)(type.GetCustomAttributes(true)[0])).TableName;
            return _Db.DbMaintenance.IsAnyTable(tableName);
        }

        /// <summary>
        /// 测试导出文件
        /// </summary>
        /// <returns></returns>
        [HttpPost("Export")]
        public async Task<dynamic> Export()
        {
            /*获取数据源
             * 返回文件流
             */
            var dataTable = await _Db.Queryable<User>().Where(it => it.DeleteMark == null).Select(it => new User { UserCode = it.UserCode, Address = it.Address }).ToDataTableAsync();
            DataTable dt_New = new DataTable();
            dt_New.Columns.Add("用户名", typeof(string));
            dt_New.Columns.Add("地址", typeof(string));
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                DataRow _row = dt_New.NewRow();
                _row["用户名"] = dataTable.Rows[i]["UserCode"].ToString();
                _row["地址"] = dataTable.Rows[i]["Address"].ToString();
                dt_New.Rows.Add(_row);
            }
            if (dt_New.Rows.Count == 0)
            {
                return Content(RR.Error("未发现可导出数据"));
            }
            else
            {
                string SheetName = "用户数据";
                string FileName = "用户数据.xls";
                string DirectoryName = "用户数据";
                var downlod = ToExcel.ExcelExport(dt_New, SheetName, FileName, DirectoryName, "admin", "default");
                return RR.Success("导出成功!", downlod);
            }

        }

        /// <summary>
        /// 导入数据.
        /// </summary>
        /// <returns></returns>
        [HttpGet("Import")]
        public async Task<dynamic> Import()
        {
            return "";
        }

        //导入预览
        //模板下载
        [HttpGet("TemplateDownload")]
        public async Task<dynamic> TemplateDownload()
        {
            /*首先获取指定文件下的模板xlsx
             * 
             * 返回生成的url
             */
            //生成模板名
            string fileName = "测试.xls";
            //模板路径
            var filepath = Path.Combine(@"D:\qxAPI\Test\TemplateFile", "test.xls") ;
            //保存路径
            var addFilePath = Path.Combine(@"D:\qxAPI\Test\TemporaryFile", fileName);
            if (!Directory.Exists(addFilePath))
            {
                //读取指定文件夹下的文件信息
                FileStream fsWrite = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write);
                //存到字节数组中
                //然后一个stream 读取字节数组到指定文件夹
               // Stream stream =new 
                //读取到文件后保存到临时文件夹下


            }
            return new { name = fileName, url = "" };
        }
        //文件上传
    }
    public class Test
    {
        public string id { get; set; }

        public string name { get; set; }

        public string userCode { get; set; }

        public string fileInfo { get; set; }
    }

    public class Process
    {
        public string id { get; set; }

        public string name { get; set; }

        public string userCode { get; set; }

        public List<FileControlsModel> fileInfo { get; set; }
    }

    //public class FileDto
    //{
    //    public string fileJson { get; set; }
    //    public List<FileControlsModel> fileInfo { get; set; }
    //}

}
